Connecting to Web Sources and APIs in Microsoft Power BI

Course: Microsoft Power BI Training

Topic: Connecting to Web Sources and APIs

Course Link: Microsoft Power BI Training

Demo Link: Register for a Course Demo

JustAcademy Website: Visit JustAcademy

1. Introduction to Web Sources and APIs

Microsoft Power BI is a business intelligence and data visualization tool that enables users to connect to multiple data sources, transform raw data, create data models, and build interactive reports. Business data may come from Excel workbooks, databases, cloud platforms, websites, and application programming interfaces (APIs).

Connecting to web sources and APIs allows Power BI to retrieve online data and combine it with internal business information. This helps organizations analyze external information, automate reporting processes, and make data-driven decisions.

1.1 What Is a Web Source?

A web source is an online resource that provides data through a URL. Power BI can connect to supported web pages, downloadable files, and online data services using its Web connector and Power Query capabilities.

Examples include:

• HTML tables published on websites.
• CSV files hosted on web servers.
• Excel workbooks available through accessible URLs.
• JSON files containing structured records.
• XML documents containing hierarchical information.
• Public datasets published by government organizations.

1.2 What Is an API?

An API, or Application Programming Interface, enables different software applications to communicate through defined rules and requests. A web API allows a client application to request information from a remote service through an endpoint.

For example, Power BI can retrieve product details, sales transactions, inventory information, customer records, or weather data from an API when the service supports the required access method.

1.3 Why Connect to Web Sources and APIs?

• Automated retrieval: Reduce manual downloads and repetitive data entry.
• Updated information: Refresh reports when supported source data changes.
• Data integration: Combine external information with Excel files and databases.
• Business intelligence: Enrich dashboards with information from external systems.
• Efficiency: Reuse Power Query transformations across refreshes.
• Scalability: Build repeatable data preparation workflows for recurring reports.

2. Learning Objectives

After studying this topic, learners should be able to:

• Understand web sources, APIs, endpoints, and HTTP requests.
• Connect Power BI Desktop to web pages and online files.
• Retrieve data from supported REST APIs.
• Understand JSON, XML, CSV, and HTML data formats.
• Use Power Query Editor to transform online data.
• Write basic Power Query M expressions using Web.Contents().
• Understand query parameters, headers, and authentication.
• Handle nested JSON records and API pagination.
• Configure supported data refresh options.
• Troubleshoot common web connection errors.
• Apply performance, security, and data quality best practices.

3. Understanding Web Data Formats

Before connecting to an online source, identify the format in which the data is provided. Different formats require different navigation and transformation techniques.

4. Connecting Power BI to a Web Source

Power BI Desktop provides a Web connector that can retrieve data from supported URLs. Depending on the resource, Power BI may identify tables, lists, records, or downloadable file contents.

4.1 Step-by-Step Process

1. Open Microsoft Power BI Desktop.
2. Select Home > Get data.
3. Choose Web.
4. Enter the URL of the web page or online resource.
5. Select the appropriate connection option and continue.
6. Choose the required authentication method if prompted.
7. Review the available tables or data objects in Navigator.
8. Select the desired data object.
9. Choose Load to import it directly or Transform Data to prepare it in Power Query Editor.
10. Clean the data and correct the column data types.
11. Select Close & Apply to load the prepared data into the model.

4.2 Example: Importing a Web Table

Suppose a business wants to analyze a public table containing country names and population figures.

1. Copy the URL of the page containing the table.
2. Open Power BI Desktop and choose Get data > Web.
3. Enter the URL and connect.
4. Review the detected tables in Navigator.
5. Select the table containing the required information.
6. Open Power Query Editor and remove unnecessary columns.
7. Rename the columns and assign appropriate data types.
8. Load the data and create a chart or table visual.

Important: Not all websites expose data in a form that Power BI can retrieve directly. Pages that rely on complex JavaScript rendering, interactive authentication, or anti-bot protections may require a supported API or another authorized data access method.

5. Understanding REST APIs

A REST API exposes resources through endpoints, which are URLs that identify the information or operation being requested. REST APIs commonly communicate using HTTP or HTTPS and often return JSON responses.

5.1 Example of an API Endpoint

https://api.example.com/v1/products

This fictional endpoint represents a collection of products. An actual endpoint must be obtained from the API provider's documentation.

5.2 Common HTTP Methods

Power BI reporting typically uses read operations. Do not execute API operations that modify or delete production data unless that action is specifically intended and authorized.

5.3 Components of an API Request

• Base URL: The root address of the API.
• Endpoint: The specific resource path.
• Query parameters: Additional values used to filter or control the request.
• Headers: Metadata sent with the request.
• Request body: Data submitted with methods such as POST.
• Authentication: Credentials or identity information required for access.

Example URL with query parameters:

https://api.example.com/v1/orders?status=completed&limit=100

In this illustrative example, the request asks for completed orders and specifies a result limit. The actual parameter names and behavior depend on the API.

6. Connecting to a JSON API Using Power Query

JSON is a common response format for modern web APIs. Power Query can parse JSON content and transform its records or lists into tabular data.

6.1 Example JSON Response

[
  {
    "ProductID": 101,
    "ProductName": "Laptop",
    "Category": "Electronics",
    "Price": 55000
  },
  {
    "ProductID": 102,
    "ProductName": "Keyboard",
    "Category": "Accessories",
    "Price": 1500
  }
]

This response contains two product records with identifiers, product names, categories, and prices.

6.2 Power Query M Code Example

let
    Source = Json.Document(
        Web.Contents(
            "https://api.example.com/v1/products"
        )
    ),
    ProductTable = Table.FromRecords(Source),
    ChangedTypes = Table.TransformColumnTypes(
        ProductTable,
        {
            {"ProductID", Int64.Type},
            {"ProductName", type text},
            {"Category", type text},
            {"Price", type number}
        }
    )
in
    ChangedTypes

Note: The endpoint above is fictional. Replace it with an actual API URL that you are authorized to access. This example assumes that the API returns a top-level JSON list of records. If the response has a different structure, the transformation steps must be adjusted.

6.3 Explanation of the M Code

6.4 How to Enter the Code

1. Open Power BI Desktop.
2. Select Transform Data.
3. Create a blank query in Power Query Editor.
4. Open Home > Advanced Editor.
5. Enter the M code and replace the sample endpoint.
6. Confirm that the response structure matches the code.
7. Configure the required source credentials.
8. Preview the resulting table and correct any errors.
9. Select Close & Apply.

7. Working with Nested JSON Data

Some APIs return nested records and lists. For example, an order may contain a customer record and multiple order items.

7.1 Nested JSON Example

{
  "orderId": 5001,
  "customer": {
    "name": "Aarav",
    "city": "Mumbai"
  },
  "items": [
    {
      "product": "Monitor",
      "quantity": 2
    },
    {
      "product": "Mouse",
      "quantity": 1
    }
  ]
}

7.2 Transformation Steps

1. Parse the response using Json.Document().
2. Inspect the top-level record.
3. Convert the record into a table when necessary.
4. Expand the customer record to access name and city.
5. Expand the items list when item-level analysis is required.
6. Expand each item record to retrieve product and quantity.
7. Retain the order identifier to preserve relationships.
8. Check for duplicate records and confirm the resulting row count.

Expanding a list of order items can create multiple rows for the same order. This is normal for one-to-many data, but identifiers and relationships must be modeled correctly to avoid incorrect totals.

8. Using Query Parameters in Power BI

Query parameters allow an API request to specify filters, dates, categories, page numbers, or result limits.

8.1 Example URL

https://api.example.com/v1/sales?year=2026&region=west

This example requests sales information for a specified year and region, assuming the API supports these parameters.

8.2 Power Query Example

let
    Source = Json.Document(
        Web.Contents(
            "https://api.example.com",
            [
                RelativePath = "v1/sales",
                Query = [
                    year = "2026",
                    region = "west"
                ]
            ]
        )
    )
in
    Source

Using a stable base URL with RelativePath and Query can make API requests easier to maintain and may improve compatibility with Power BI Service refresh. The endpoint and parameter names must match the API documentation.

8.3 Benefits of Query Parameters

• Retrieve only relevant records.
• Reduce unnecessary data transfer.
• Filter information by date, region, category, or status.
• Make queries easier to reuse and maintain.
• Improve efficiency when working with large datasets.

9. API Authentication and Credentials

Many APIs require authentication to ensure that only authorized users or applications can access data. The available authentication methods depend on the source and the connector.

9.1 Common Authentication Methods

9.2 Managing Credentials

1. Connect to the API or web source.
2. Select the authentication method supported by the service.
3. Enter the required credentials or complete the supported sign-in process.
4. Verify that the source returns the expected data.
5. Use File > Options and settings > Data source settings in Power BI Desktop to review stored source permissions.
6. After publishing, configure credentials in Power BI Service where required.
7. Test refresh and check the refresh history for failures.

Security best practice: Never expose passwords, secret API keys, or access tokens in reports, public repositories, or shared documentation. Use supported credential management mechanisms rather than hardcoding secrets in query text.

10. Using HTTP Headers

Some APIs require headers to identify the expected response format, specify content types, or provide other request metadata. Headers should follow the API provider's documentation.

10.1 Example Power Query Code

let
    Source = Json.Document(
        Web.Contents(
            "https://api.example.com",
            [
                RelativePath = "v1/products",
                Headers = [
                    Accept = "application/json"
                ]
            ]
        )
    )
in
    Source

This example requests JSON-formatted content from a fictional endpoint. It does not implement authentication. Additional headers should be added only when necessary and supported by the selected authentication method.

10.2 Header Best Practices

• Use HTTPS whenever available.
• Follow the API documentation for required headers.
• Avoid exposing sensitive credentials in query text.
• Use Power BI's supported credential mechanisms where appropriate.
• Confirm that the API permits the requested operations.

11. Understanding API Pagination

Pagination divides a large result set into smaller responses. It helps APIs handle large volumes of data and reduces the size of individual requests.

11.1 Common Pagination Types

• Page-based: Uses a page number and a page size.
• Offset-based: Uses a starting offset and a result limit.
• Cursor-based: Uses a token to retrieve the next set of records.
• Next-link-based: Provides a URL for the next page.

11.2 Example

https://api.example.com/v1/orders?page=1&limit=100
https://api.example.com/v1/orders?page=2&limit=100
https://api.example.com/v1/orders?page=3&limit=100

The example illustrates page-based pagination. The actual API may use a different parameter structure.

11.3 Steps for Handling Pagination

1. Read the API documentation to identify the pagination mechanism.
2. Determine how the API indicates the final page.
3. Create a Power Query function to retrieve one page.
4. Request additional pages using the required page number, cursor, or next link.
5. Combine the results into a single table.
6. Handle empty responses and request errors.
7. Check for duplicate or missing records.
8. Test the query with a small dataset before retrieving large volumes.

12. Data Transformation Using Power Query

Data retrieved from websites and APIs often requires cleaning before analysis. Power Query provides tools for preparing the data for a reliable Power BI model.

12.1 Common Transformation Tasks

• Promote the first row to headers when appropriate.
• Rename columns clearly.
• Remove unnecessary columns and rows.
• Filter data using business rules.
• Replace or handle null values appropriately.
• Convert text dates to date or datetime types.
• Convert numeric text to numbers.
• Expand nested records and lists.
• Remove duplicates when justified by the data rules.
• Merge or append data from multiple sources.

12.2 Example Data Cleaning Workflow

1. Inspect the retrieved data.
2. Identify the fields required for reporting.
3. Remove irrelevant columns.
4. Rename the remaining columns.
5. Convert date and numeric fields to appropriate types.
6. Check invalid values and missing information.
7. Validate row counts and important totals against the source.
8. Load the cleaned data into the Power BI model.

13. Combining API Data with Other Sources

Power BI can combine external API data with internal datasets to provide a more complete business view.

13.1 Business Example

A retail organization wants to compare sales performance with product information retrieved from an API and targets stored in Excel.

13.2 Integration Steps

1. Connect to the sales database.
2. Connect to the API and retrieve product details.
3. Import the targets workbook.
4. Standardize identifiers and relevant data types.
5. Create relationships between tables using suitable keys.
6. Create measures for actual sales, targets, and variance.
7. Build report visuals by product, category, and time period.
8. Validate the results against the original data sources.

Use a well-designed data model with appropriate relationships. Combining tables without considering their level of detail can result in duplicated values and incorrect aggregations.

14. Refreshing Web and API Data

Connecting to a data source establishes the initial connection. If the online data changes, Power BI must refresh the data to retrieve updated records.

14.1 Refresh in Power BI Desktop

1. Open the Power BI report.
2. Select Refresh on the Home ribbon.
3. Allow Power Query to execute the requests.
4. Review the refresh status and error messages.
5. Verify that updated records appear in the model.
6. Check important totals and visuals.

14.2 Refresh in Power BI Service

1. Publish the report or semantic model to the appropriate workspace.
2. Open the semantic model settings in Power BI Service.
3. Configure the required data source credentials.
4. Check whether a gateway is required.
5. Configure scheduled refresh when supported.
6. Run a test refresh.
7. Review refresh history and resolve any errors.

Important: A query that works in Power BI Desktop may not refresh successfully in Power BI Service. Authentication support, dynamic data source URLs, gateway requirements, API restrictions, and token expiration can affect refresh. Microsoft's Web connector documentation also identifies differences in authentication support between Desktop and the service.

15. Common Web and API Connection Errors

15.1 Troubleshooting Steps

1. Verify that the endpoint is correct.
2. Check whether the source is accessible using an approved testing method.
3. Review the authentication and query parameters.
4. Inspect the raw response and confirm its format.
5. Review Power Query's Applied Steps to locate the failing operation.
6. Check stored credentials and privacy settings.
7. Test the query using a smaller dataset.
8. Test the refresh separately in Power BI Service.

16. Performance Optimization

API queries can become slow when they retrieve unnecessary information, make too many requests, or process large responses. The following practices can improve efficiency.

• Use server-side filters: Retrieve only the required records when supported.
• Handle pagination efficiently: Avoid missing or duplicating records.
• Reduce repeated requests: Avoid unnecessary retrieval of identical data.
• Use stable URLs: Prefer suitable combinations of a fixed base URL, RelativePath, and Query.
• Apply suitable transformations: Remove unnecessary fields and correct data types.
• Respect rate limits: Follow the API provider's request limits.
• Monitor response sizes: Avoid importing more information than the report requires.
• Choose suitable refresh schedules: Align refresh frequency with business requirements.
• Evaluate incremental refresh: Use it when the source and model support the required incremental retrieval pattern.

16.1 Query Folding

Query folding occurs when Power Query translates supported transformations into operations performed by the source system. Many APIs offer limited query folding compared with relational databases.

When a transformation cannot be pushed to the source, Power Query may need to retrieve data before processing it locally. Filtering at the API level can therefore be especially useful when working with large responses.

17. Security and Data Privacy

External data connections must be managed carefully to protect business information and credentials.

• Use HTTPS whenever supported.
• Never publish passwords, API secrets, or access tokens in report content.
• Use supported credential storage and authentication methods.
• Retrieve only the information required for the intended analysis.
• Review the API provider's terms of use and privacy policy.
• Set appropriate data source privacy levels.
• Check whether combining sources could expose confidential information.
• Follow organizational data governance policies.
• Rotate credentials when required by security policies.
• Confirm that automated access is permitted by the source provider.

18. Practical Project: Product Analytics Using a Web API

Project Objective: Create a Power BI report using product information retrieved from an authorized API. The project covers data connectivity, JSON transformation, modeling, and visualization.

18.1 Requirements

• Power BI Desktop.
• A public API or authorized API account.
• Internet access.
• Basic knowledge of Power Query.
• An optional Excel file containing sales transactions.

18.2 Project Workflow

1. Select an API that permits the intended data access.
2. Read its documentation and identify the endpoint and authentication requirements.
3. Connect through the Web connector or Power Query M.
4. Parse the JSON response.
5. Expand the required records into a table.
6. Rename columns and assign appropriate data types.
7. Handle missing values and remove unnecessary fields.
8. Load the table into the Power BI model.
9. Optionally import sales data and create relationships using a product identifier.
10. Create visuals and validate their calculations.
11. Publish the report and configure refresh if supported.

18.3 Suggested Visuals

18.4 Expected Learning Outcome

Learners will understand how to retrieve external data, transform API responses, build a data model, and develop an interactive report using Power BI.

19. Practice Exercises

Exercise 1: Connect to a Web Page

• Find a public web page containing an accessible data table.
• Connect using Get Data > Web.
• Load the table into Power Query.
• Rename the columns and correct data types.
• Create a report visual.

Exercise 2: Import JSON from an API

• Select a documented API that permits your intended access.
• Retrieve the JSON response.
• Inspect its records, lists, and nested structures.
• Convert the response into a table.
• Validate the results against the source response.

Exercise 3: Use Query Parameters

• Identify an API that supports filtering.
• Create a query using RelativePath and Query.
• Retrieve a filtered subset of records.
• Compare the result with the unfiltered response.

Exercise 4: Troubleshoot an API

• Test an endpoint in a practice environment.
• Observe errors caused by an incorrect URL or missing credentials.
• Identify the cause of each error.
• Document the steps required to resolve it.

20. Interview Questions and Answers

Q1. What is a web source in Power BI?

Answer: A web source is an online resource from which Power BI can retrieve supported data, such as a web table, JSON file, CSV file, or online service.

Q2. What is an API?

Answer: An API is an interface that allows software applications to communicate using defined requests and responses.

Q3. What is a REST API?

Answer: A REST API exposes resources through endpoints and commonly uses HTTP methods to retrieve or modify information.

Q4. What is JSON?

Answer: JSON is a structured data format that represents information through objects, arrays, strings, numbers, Boolean values, and null values.

Q5. What is Power Query M?

Answer: Power Query M is the language used to define data retrieval and transformation steps in Power Query.

Q6. What does Web.Contents() do?

Answer: It retrieves content from a web resource and supports options for query parameters, headers, relative paths, and timeouts.

Q7. What does Json.Document() do?

Answer: It parses JSON content into Power Query values such as lists and records.

Q8. What is API pagination?

Answer: Pagination divides a large dataset into smaller responses. Power Query can retrieve the pages and combine the results.

Q9. Why is authentication required?

Answer: Authentication identifies the user or application requesting data from a protected service.

Q10. What is the difference between 401 and 403 errors?

Answer: A 401 error generally indicates missing or invalid authentication. A 403 error generally indicates that access is forbidden.

Q11. Why might an API work in Power BI Desktop but fail in Power BI Service?

Answer: Differences in authentication support, dynamic URLs, gateway requirements, network access, or refresh compatibility may cause the failure.

Q12. How can API performance be improved?

Answer: Retrieve only required data, use supported filters, manage pagination efficiently, respect rate limits, and avoid unnecessary requests.

Q13. Can Power BI connect to an API requiring an API key?

Answer: Yes, when the API and connector support a compatible authentication method. The key must be managed securely, and service refresh compatibility should be verified.

Q14. What is the difference between loading and transforming data?

Answer: Loading imports data into the model, while transforming prepares it by filtering, cleaning, reshaping, or changing data types.

Q15. What should you check when a JSON query fails?

Answer: Check the endpoint, authentication, HTTP response, JSON structure, and Power Query transformation steps.

21. Quick Revision Checklist

• Understand web sources and APIs.
• Identify HTML, CSV, JSON, XML, and Excel formats.
• Connect to web resources through Power BI Desktop.
• Use Navigator and Power Query Editor to inspect and transform data.
• Understand endpoints, HTTP methods, headers, and query parameters.
• Use Web.Contents() to retrieve web content.
• Use Json.Document() to parse JSON.
• Transform nested records and lists into tables.
• Handle pagination without missing or duplicating records.
• Protect API keys and credentials.
• Test refresh in Power BI Desktop and Power BI Service.
• Troubleshoot common connection and performance issues.

22. Official Learning Resources

• Microsoft Learn: Power Query Web Connector — connection steps, authentication, and supported capabilities.
• Microsoft Learn: Web.Contents Function — Power Query M syntax and web request options.
• Microsoft Learn: Troubleshooting the Web Connector — connection and refresh troubleshooting.

23. Conclusion

Connecting to web sources and APIs is an essential Power BI skill because business information often exists outside traditional databases and spreadsheets. The Web connector and Power Query allow users to retrieve supported online content, parse structured responses, transform data, and integrate external information into business intelligence reports.

Successful API integration requires a clear understanding of data formats, endpoints, authentication, pagination, refresh compatibility, performance, and security. By practicing these techniques, learners can create reliable, maintainable, and informative Power BI dashboards using external data.

Explore the Course: Microsoft Power BI Training at JustAcademy

Register for a Demo: Book a Course Demo

Visit: JustAcademy Official Website